package us.gaaoc.framework.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;

import us.gaaoc.framework.model.CaseRecord;
import us.gaaoc.framework.model.DocumentStatus;
import us.gaaoc.framework.model.Documents;
import us.gaaoc.framework.model.util.DocumentsCountByType;
import us.gaaoc.framework.model.util.DocumentsCountPerMonth;

/**
 * 
 * @author hea
 * @date Apr 28, 2006
 * @version: GAJE 1.5
 *
 */
public class DocumentsDAOHibernate extends GenericHibernateDAO<Documents, String, DocumentsDAO>
implements  DocumentsDAO{

	protected DocumentsDAOHibernate() {}

	public Documents findDocumentsByInstanceAndCaseUuid(String docInstanceCode, String caseUuid) {
		Query q = this.getSession().createQuery("from Documents doc where doc.documentInstance.code = ? and doc.caseRecord.uuid = ?");
		q.setString(0, docInstanceCode);
		q.setString(1, caseUuid);		
		return (Documents) q.uniqueResult();
	}

	@SuppressWarnings("unchecked")
	public List<Documents> findDocumentsByCaseUuid(String caseUuid) {
		Query q = this.getSession().createQuery("from Documents doc where doc.caseRecord.uuid = ?");
		q.setString(0, caseUuid);	
		return q.list();
	}
	
	public void updateDocumentStatus(String uuid, DocumentStatus status) {
		//Documents doc = this.findById(uuid, true);
		Documents doc = this.findById(uuid);
		doc.setStatus(status);
		this.makePersistent(doc);
	}
	
	@SuppressWarnings("unchecked")
	public List<Documents> findDocumentsbyMaxVersion(String caseRecordUuid){		
		Query q = this.getSession().createQuery("from Documents doc " +
				"where doc.documentInstance.code <> ? and doc.physicalType = ? and " +
				" doc.version in (Select max(doc1.version) from Documents doc1)"+
				" and doc.caseRecord.uuid = ? "+
				"group by doc.documentInstance.code");		
		q.setString(0,"1");
		q.setString(1,"xml");
		q.setString(2,caseRecordUuid);
		return q.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Documents> getPDFDocuments(String caseRecordUuid){
		Query q = this.getSession().createQuery("from Documents doc " +
				"where doc.physicalType = ? and doc.caseRecord.uuid = ? ");		
		q.setString(0,"pdf");
		q.setString(1,caseRecordUuid);
		return q.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Documents> findDocumentsByDocStatusCode(int code) {
		LOG.info("Inside the findDocummentsByDocStatusCode method of the DocumentsDAOHibernate.." + new Integer(code).toString());	
		Query q = this.getSession().createQuery("from Documents doc where doc.documentStatus.code = ? ");
		q.setInteger(0, code);
		LOG.info("Back inside the findDocumentsByDocStatusCode after the query is returned...");
		return q.list();
	}

	public void removeDocumentFromListByCaseRecordUuidAndDocumentInstanceCode(String caseRecordUuid, String documentInstanceCode) {
		String sql = "DELETE FROM Documents d WHERE d.documentInstanceCode = ? and d.caseRecordUuid = ?";		
		int deletedEntities = this.getSession().createQuery(sql).setString( 0, documentInstanceCode).setString(1, caseRecordUuid).executeUpdate();
		 LOG.info("deleted rows "+deletedEntities);
	}
	
	public void enableDocDeleteFlag(Documents doc) {
		doc.setUserDeleteFlag(true);
		String sql = "UPDATE Documents d set userDeleteFlag = 1 WHERE d.uuid = ? ";		
		int updatedEntities = this.getSession().createQuery(sql).setString( 0, doc.getUuid()).executeUpdate();

		//this.getSession().update(doc);
		//this.makePersistent(doc);
	}

	public void deleteDocument(Documents doc) {
		String hqlDelete = "delete Documents d WHERE d.uuid=?";	

		int deletedEntities = this.getSession().createQuery( hqlDelete )
		        .setString( 0, doc.getUuid()).executeUpdate();
		LOG.info("deleted rows "+deletedEntities);
		
	}
	
	/**
	 * This query returns the number of documents of a case record in GAJE system that are imported in the local cms
	 * This number is needed in order to import an additional document
	 * @param caseRecord
	 * @return number of documents already imported into CMS of aC case record 
	 */	

	@SuppressWarnings("unchecked")
	public int findTheNumberOfDocumentsAlreadyImportedIntoCMSOfACaseRecord(
			CaseRecord caseRecord) {
		List<Documents> docList = new ArrayList<Documents>();
		Query q = this.getSession().createQuery("from Documents doc where doc.status.code = ?  and doc.caseRecord.uuid = ? ");
		q.setInteger(0, 2);
		q.setSerializable(1, caseRecord.getUuid());
		docList =  q.list();
		return docList.size();
	}
	
	/**
	 *  change the status of a document 
	 *  @param docUuid
	 *  @param docStatus
	 */
	public void UpdateDocumentByStatus(String docUuid, DocumentStatus docStatus) {
		Documents d = this.findById(docUuid);
		d.setStatus(docStatus);
		this.makePersistent(d);
	}

	//Methods Added by Bhawana
	@SuppressWarnings("unchecked")
	public int countDocuments() {
		String sql="SELECT COUNT(d.uuid) " +
				"FROM documents d, " +
				"documentInstance di ," +
				"documentType dt, " +
				"documentCategory dc " +
				"WHERE d.documentInstanceCode=di.code " +
				"AND di.docTypeCode = dt.code " +
				"AND dt.docCategoryCode = dc.code " +
				"AND dc.code != 14;";
		Query q = getSession().createSQLQuery(sql);
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}

	@SuppressWarnings("unchecked")
	public int countDocumentsByCategoryExcludingDataFiles() {
		String sql="SELECT COUNT(d.uuid) " +
				/*"," +
				"	dc.description,dc.code 		*/
				"FROM documents d, " +
				"documentInstance di," +
				"	documentType dt," +
				"documentCategory dc " +
			
				"WHERE d.documentInstanceCode=di.code" +
				"	AND di.docTypeCode = dt.code " +
				"AND dt.docCategoryCode = dc.code " +
				"AND dc.code != 14 " +
				"GROUP BY dc.description " +
				"ORDER BY dc.description;";
		Query q = getSession().createSQLQuery(sql);
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}

/*	public int countDocumentsByMonth() {
		String sql="SELECT COUNT(d.uuid)  "	+
			/*	" EXTRACT(YEAR_MONTH FROM cr.instantiationDateTime) AS date, " +
				*/
	/*			"FROM caseRecord cr, documents d " +
				"WHERE cr.uuid=d.caseRecordUuid " ;
			//	+ "GROUP BY date;";
		Query q = getSession().createSQLQuery(sql);
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}*/

/*	public int countNumberOfDocumentsByCategoryAndTypeExcludingDataFiles() {
		/*String sql="SELECT COUNT(d.uuid)" +
		"FROM documents d, " +
		"documentInstance di," +
		"documentType dt," +
		"documentCategory dc" +
		"WHERE d.documentInstanceCode=di.code" +
		"AND di.docTypeCode = dt.code" +
		"AND dt.docCategoryCode = dc.code" +
		"AND dc.code != 14;";*/
		
	/*	String sql="SELECT COUNT(d.uuid) AS Quantity " +
				
		/*"	,CONCAT(dc.description, " +
				" dt.description) AS Name," +
				"CONCAT(dc.code, dt.code) AS Code " +
				*/
		/*		"FROM documents d, " +
				"documentInstance di," +
				"documentType dt," +
				"	documentCategory dc" +
				"	WHERE d.documentInstanceCode=di.code " +
				"AND di.docTypeCode = dt.code" +
				"	AND dt.docCategoryCode = dc.code" +
				"	AND dc.code != 14" +
				"	GROUP BY dt.description" +
				"	ORDER BY dc.description";
		Query q = getSession().createSQLQuery(sql);
		List l = q.list();
		BigInteger tempResultSize = (BigInteger) l.get(0);
		int resultSetSize = tempResultSize.intValue();
		return resultSetSize;
	}*/
	
	@SuppressWarnings("unchecked")
	public List<DocumentsCountPerMonth> countDocumentsByMonth() {

		String sql="SELECT MONTHNAME(instantiationDateTime) AS Date, "+
        "COUNT(d.uuid) " +
       " FROM caseRecord cr, documents d  " +
        " WHERE cr.uuid=d.caseRecordUuid " +
       " GROUP BY date";
		Query q = getSession().createSQLQuery(sql);
		List l =  q.list();
		List <DocumentsCountPerMonth> monthList=new ArrayList<DocumentsCountPerMonth>();
		@SuppressWarnings("unused")
		DocumentsCountPerMonth documentsCountPerMonth=new  DocumentsCountPerMonth();

		Iterator i = l.iterator();
		while (i.hasNext()) {
			Object[] results = (Object[]) i.next();
			byte[]  monthName = (byte[]) results[0];
			BigInteger tempCount = (BigInteger) results[1];
			String value=new String(monthName);
			int count = tempCount.intValue();
			DocumentsCountPerMonth d=new DocumentsCountPerMonth(value,count);
			monthList.add(d);
		}
		return monthList;
	
}

	@SuppressWarnings("unchecked")
	public List<DocumentsCountByType> countDocumentsByType() {
		String sql = " SELECT COUNT(d.uuid) AS Quantity, "
				+ "	CONCAT(dc.description,  " + " dt.description) AS Name, "
				+ "	CONCAT(dc.code, dt.code) AS Code	" + "	FROM documents d, "
				+ "	documentInstance di,	" + "	documentType dt,	"
				+ "	documentCategory dc "
				+ "	WHERE d.documentInstanceCode=di.code	"
				+ "	AND di.docTypeCode = dt.code	"
				+ "	AND dt.docCategoryCode = dc.code	" + "	AND dc.code != 14 	"
				+ "	GROUP BY dt.description  " + "	ORDER BY dc.description";
		Query q = getSession().createSQLQuery(sql);
		List l = q.list();
		List<DocumentsCountByType> docList = new ArrayList<DocumentsCountByType>();
		@SuppressWarnings("unused")
		DocumentsCountByType documentsCountByType = new DocumentsCountByType();

		Iterator i = l.iterator();
		while (i.hasNext()) {
			 Object[] results = (Object[]) i.next();
			   BigInteger tempCount = (BigInteger) results[0];
			   int count = tempCount.intValue();
			   //byte[]  docName = (byte[]) results[1];		
			   String value=(String)(results[1]);		
			   DocumentsCountByType d=new DocumentsCountByType(value,count);
			   docList.add(d);
		}
		return docList;

	}
	
	public Documents getTextDocument(String caseUuid) {
		String csv = "text/x-comma-separated-values";
		String msword = "application/msword";
		String text = "text";
		Query q = this.getSession().createQuery("from Documents doc " +
		"where (doc.physicalType = ? or doc.physicalType = ? or doc.physicalType = ?)and doc.caseRecord.uuid = ? ");		
		q.setString(0,csv);
		q.setString(1, msword);
		q.setString(2, text);
		q.setString(3,caseUuid);
		return (Documents)q.uniqueResult();
	}
	
	/*
	 * (non-Javadoc)
	 * @see us.gaaoc.framework.dao.GenericHibernateDAO#findAll()
	 */
	@SuppressWarnings("unchecked")
	public List<Documents> findAll(int record) {
		
		String getAllDocumentsQuery = "from Documents d";
		List<Documents> allDocuments;
		
		Query databaseResult = this.getSession().createQuery(getAllDocumentsQuery).setFirstResult(record).setMaxResults(1000);
		allDocuments = databaseResult.list();
		
		return allDocuments;
	}
}